# -*- coding: utf-8 -*-
 ##############################################
 ##                                            ##
 ##               Base Data model               ##
 ##                                             ##
 ##                                             ##
 ##              from Basiq Series              ##
 ##           by Críptidos Digitales            ##
 ##                 GPL (c)2008                 ##
  ##                                            ##
    ##############################################

"""
"""

import sys
import datetime
from decimal import Decimal

import psycopg2 as adapter
import psycopg2.extras as extras
adapter.extensions.register_type(adapter.extensions.UNICODE)
adapter.extensions.register_type(adapter.extensions.UNICODEARRAY)


class __local: pass
__local.conn = None
__local.cursor = None


def getAception(**filters):
    command = u"""SELECT ac.*,
    products.id AS product_id,
    products.costopromedio,
    products.margen,
    products.impuesto,
    products.minimo,
    products.maximo,
    products.actual,
    products.unidad_id,
    attributes.name AS clasification,
    products.clasification_id,
    products.kind_id,
    kinds.name AS kinds_name,
    prices.ids AS prices_id, prices.values AS prices_value, prices.factors1 AS prices_factor1, prices.range_ids AS prices_kind_id, prices.factors2 AS prices_factor2,
    lines.names AS lines_name,
    lines.ids AS lines_id
FROM aceptions AS ac, products, attributes, attributes AS kinds,
    (SELECT product_id,
    array_agg(at1.id) AS ids, array_agg(at1.value) AS values, array_agg(at1.attribute_id) AS range_ids, array_agg(at1.factor1) AS factors1, array_agg(at1.factor2) as factors2
    FROM productattributes as at1
    WHERE name='precio' AND status='actual'
    GROUP BY product_id
    ) AS prices,

    (SELECT product_id, array_agg(attributes.name) AS names, array_agg(attributes.id) AS ids
    FROM productattributes, attributes
    WHERE productattributes.attribute_id=attributes.id AND productattributes.name='línea'
    GROUP BY product_id
    ) AS lines """

    filtersText = u"""
    ac.product_id=products.id AND ac.product_id=prices.product_id AND ac.product_id=lines.product_id AND products.classification_id=atributos.id AND products.kind_id = kinds.id AND """

    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += u"%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += u"%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += u"%s is null AND " % filter
        else:
            filtersText += u"ac.%s=%s AND " % (filter, filters[filter])

    command += u"WHERE %s " % filtersText.rstrip(u"AND ")

    #~ print "-++\n", command.encode('utf8')

    query(command)
    aception = fetchOne()

    #~ print "--+\n", aception

    if aception:
        aception['prices'] = []
        for index, id in enumerate(aception['prices_id']):
            price = {'id':id, 'valor':Decimal(aception['prices_value'][index]), 'kind_id':aception['prices_kind_id'][index]}
            price['factor1'] = aception['prices_factor1'][index]
            try: price['factor1'] = Decimal(price['factor1'])
            except: pass
            price['factor2'] = aception['prices_factor2'][index]
            try: price['factor2'] = Decimal(price['factor2'])
            except: pass
            aception['prices'].append(price)
        aception.pop('prices_id')
        aception.pop('prices_value')
        aception.pop('prices_factor1')
        aception.pop('prices_factor2')
        aception.pop('prices_kind_id')

        aception['lines'] = [{'id':id, 'nombre':aception['lineas_nombre'][index]} for index, id in enumerate(aception['lineas_id'])]
        aception.pop('lineas_id')
        aception.pop('lineas_nombre')

    return aception


def aceptionExists(**filtros):
    try:
        if 'codigo' in filtros.keys():
            registro = session().query(Acepcion).filter(Acepcion.codigo==unicode(filtros['codigo'])).filter(Acepcion.rol_id==filtros['rol_id']).filter(Acepcion.status==u'actual').one()
        else:

            registro = session().query(Acepcion).filter(Acepcion.producto_id==filtros['producto_id']).filter(Acepcion.rol_id==filtros['rol_id']).filter(Acepcion.status==u'actual').one()
        return registro
    except:
        return None


def getAceptions(**filters):
    command ="""SELECT ac.id, ac.rol_id, ac.codigo, ac.nombre, ac.costo, ac.descuentoindividual, ac.descuentogeneral, ac.referencia, products.id AS producto_id, products.tipo_id, products.unidad_id, products.minimo, products.maximo, products.actual, products.costoPromedio, products.impuesto, products.margen, atributos.nombre AS clasificacion, prices.*, y.lineas_id, y.lineas_nombre
FROM acepciones AS ac, products, atributos,
	(SELECT producto_id, array_agg(at1.id) AS prices_id, array_agg(at1.valor) AS prices_valor, array_agg(at1.factor1) AS prices_factor1, array_agg(at1.factor2) AS prices_factor2, array_agg(at1.atributo_id) AS prices_tipoid
	FROM atributosproducto as at1
	WHERE nombre='precio' AND status='actual'
	GROUP BY producto_id
	) AS prices,

	(SELECT producto_id, array_agg(atributos.id) AS lineas_id, array_agg(atributos.nombre) AS lineas_nombre
	FROM atributosproducto, atributos
	WHERE atributosproducto.atributo_id=atributos.id AND atributosproducto.nombre='línea'
	GROUP BY producto_id
	) AS y """

    filtersText = """ac.producto_id = products.id AND products.clasificacion_id = atributos.id AND ac.producto_id = prices.producto_id AND ac.producto_id = y.producto_id AND ac.status='actual' AND """

    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "ac.%s=%s AND " % (filter, filters[filter])

    command += "WHERE %s " % filtersText.rstrip("AND ")

    #~ print command

    query(command)
    aceptions = fetchAll()

    for aception in aceptions:
        aception['prices'] = []
        for index, id in enumerate(aception['prices_id']):
            price = {'id':id, 'valor':Decimal(aception['prices_valor'][index]), 'tipo_id':aception['prices_tipoid'][index]}
            price['factor1'] = aception['prices_factor1'][index]
            try: Decimal(price['factor1'])
            except: pass
            price['factor2'] = aception['prices_factor2'][index]
            try: Decimal(price['factor2'])
            except: pass
            aception['prices'].append(price)
        aception.pop('prices_id')
        aception.pop('prices_valor')
        aception.pop('prices_factor1')
        aception.pop('prices_factor2')
        aception.pop('prices_tipoid')

        #~ aception['prices'] = [{'id':id, 'valor':Decimal(aception['prices_valor'][index]), 'factor1':Decimal(aception['prices_factor1'][index]), 'factor2':Decimal(aception['prices_factor2'][index]), 'tipo_id':aception['prices_tipoid'][index]} for index, id in enumerate(aception['prices_id'])]
        #~ aception.pop('prices_id')
        #~ aception.pop('prices_valor')
        #~ aception.pop('prices_factor1')
        #~ aception.pop('prices_factor2')
        #~ aception.pop('prices_tipoid')

        aception['lines'] = [{'id':id, 'nombre':aception['lineas_nombre'][index]} for index, id in enumerate(aception['lineas_id'])]
        aception.pop('lineas_id')
        aception.pop('lineas_nombre')

    return aceptions


def setAception(**data):
    if 'id' in data.keys():
        dbData = data
        data = getOne('acepciones', id=data.pop('id'))
        data['status'] = 'obsoleta'
        data['fechabaja'] = datetime.datetime.today()

        id = dbData.pop('id')
        dbData['fechaAlta'] = data['fechabaja']
        command = """UPDATE acepciones SET """
        valuesText = ""
        for value in dbData.keys():
            if type(dbData[value]) in [str, unicode, datetime.datetime]:
                valuesText += "%s = '%s', " % (value, dbData[value])
            else:
                valuesText += "%s = %s, " % (value, dbData[value])
        command += "%s WHERE id=%s" % (valuesText.rstrip(', '), id)
        execute(command)

    command = """INSERT INTO acepciones """
    fieldsText = ""
    valuesText = ""
    for key in data.keys():
        if type(data[key]) in [str, unicode, datetime.datetime]:
            fieldsText += "%s, " % key
            valuesText += "'%s', " % data[key]
        elif data[key] == None:
            fieldsText += "%s, " % key
            valuesText += "Null, "
        else:
            fieldsText += "%s, " % key
            valuesText += "%s, " % data[key]
    command = "%s (%s) VALUES (%s)" % (command, fieldsText.rstrip(", "), valuesText.rstrip(", "))

    #~ print command.encode('utf8')

    execute(command)


def getAceptionsSelect(**filters):
    command = u"""SELECT ac.id, ac.rol_id, ac.codigo, ac.nombre, ac.costo, ac.descuentoindividual, ac.descuentogeneral, products.id AS producto_id, products.actual, atributos.nombre AS clasificacion, x.precios_id, x.precios_valor, x.precios_factor1, x.precios_tipoid, y.lineas
FROM acepciones AS ac, products, atributos,
    (SELECT producto_id, array_agg(at1.id) AS precios_id, array_agg(at1.valor) AS precios_valor, array_agg(at1.factor1) AS precios_factor1, array_agg(at1.atributo_id) AS precios_tipoid
    FROM atributosproducto as at1
    WHERE nombre='precio' AND status='actual'
    GROUP BY producto_id
    ) AS x,

    (SELECT producto_id, array_agg(atributos.nombre) AS lineas
    FROM atributosproducto, atributos
    WHERE atributosproducto.atributo_id=atributos.id AND atributosproducto.status='actual' AND atributosproducto.nombre='línea'
    GROUP BY producto_id
    ) AS y """

    filtersText = """ac.status='actual' AND ac.producto_id=products.id AND products.clasificacion_id=atributos.id AND ac.producto_id=x.producto_id AND ac.producto_id=y.producto_id AND """

    #~ orderText = """ac.id, products.id """
    orderText = ''
    if 'order' in filters.keys():
        text = ''
        for filter in filters.pop('order').split(', '):
            text += "ac.%s, " % filter
        orderText += "ORDER BY %s" % text.rstrip(', ')

    for filter in filters.keys():
        if ' LIKE' in filter.upper():
            filtersText += "UPPER(ac.%s) LIKE '%s%%' AND " % (filter.upper().rstrip('LIKE'), filters[filter].upper())
        elif type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "ac.%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "%s=%s AND " % (filter, filters[filter])

    command += "WHERE %s " % filtersText.rstrip("AND ")
    command += "%s " % orderText

    #~ print "getAceptionsSelect()\n    %s" % command.encode('utf8')

    query(command)
    aceptions = fetchAll()

    for aception in aceptions:
        aception['prices'] = [{'id':id, 'valor':Decimal(aception['precios_valor'][index]), 'factor1':aception['precios_factor1'][index], 'tipo_id':aception['precios_tipoid'][index]} for index, id in enumerate(aception['precios_id'])]
        aception.pop('precios_id')
        aception.pop('precios_valor')
        aception.pop('precios_factor1')
        aception.pop('precios_tipoid')

    return aceptions


def getAddresses(**filters):
    data = getAll('addresses', **filters)
    return data


def getAttribute(**filters):
    # print "baseModel,getAttribute()", filters

    LIKEInKey = [x for x in filters.keys() if 'LIKE' in x]
    if LIKEInKey:
        dataZero = [attribute for attribute in db.attributes if len(filters) == len( ['match' for key in filters.keys() if key.replace('LIKE', '') in attribute.keys() and filters[key] in "%s" % attribute[key.replace('LIKE', '')]])]
    else:
        dataZero = [attribute for attribute in __local.attributes if len(filters) == len( ['match' for key in filters.keys() if key in attribute.keys() and filters[key]==attribute[key] ] )]

    if dataZero:
        return dataZero[0]
    else:
        return None


def setAttribute(**filters):

    def modify():
        id = attr['id']
        command = """UPDATE attributes SET """
        setText = ""
        for filter in filters.keys():
            if type(filters[filter]) in [str, unicode]:
                setText += "%s='%s', " % (filter, filters[filter])
            else:
                setText += "%s=%s, " % (filter, filters[filter])
        command += "%s WHERE id=%s " % (setText.rstrip(', '), id)
        execute(command)

    if 'id' in filters.keys():
        attr = getAttribute(id=filters.pop('id'))
        if attr:
            attr.update(filters)
            self.modify()
            return attr

    attr = getAttribute(category=filters['grupo'], name=filters['nombre'])
    if attr:
        attr.update(filters)
        modify()
        return attr

    # No existe, crear
    command = """INSERT INTO attributes """

    fieldsText = ""
    valuesText = ""
    for filter in filters.keys():
        if type(filters[filter]) in [str, unicode]:
            fieldsText += "%s, " % filter
            valuesText += "'%s', " % filters[filter]
        else:
            fieldsText += "%s, " % filter
            valuesText += "%s, " % filters[filter]

    command = "%s (%s) VALUES (%s)" % (command, fieldsText.rstrip(", "), valuesText.rstrip(", "))

    execute(command)

    command = """SELECT * FROM attributes """

    filtersText = ''
    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        else:
            filtersText += "%s=%s AND " % (filter, filters[filter])

    if filtersText:
        command += "WHERE %s " % filtersText.rstrip("AND ")

    query(command)
    attribute = fetchOne()

    __local.attributes.append(attribute)

    return attribute



def getAttributes(**filters):
    orderText = ""
    if 'order' in filters.keys():
        orderText = filters.pop('order')

    filtersBody = ""
    for filter in filters:
        if type(filters[filter]) in [int]:
            filtersBody += "x['%s']==%s and " % (filter, filters[filter])
        elif 'LIKE' in filter:
            filtersBody += "'%s' in x['%s'] and " % (filters[filter], filter.replace('LIKE', ''))
        else:
            filtersBody += "x['%s']==u'%s' and " % (filter, filters[filter])
    if not filtersBody:
        filtersBody += "id != ''"
    exec(u"""data = [x for x in __local.attributes if %s]""" % filtersBody.rstrip('and '))

    return data


def loadAttributes():
    #~ print "baseModel.loadAttributes()"
    __local.attributes = getAll('attributes')


def getDocumento(**filters):
    if 'proceso_id' in filters.keys() or 'operacion_id' in filters.keys():
        command = """SELECT documentV.* FROM documentV JOIN operaciones_documentos USING (documento_id) JOIN proceso_operacion USING (operacion_id) JOIN operaciones USING (operacion_id) """

        filtersText = ""
        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)
            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command += "WHERE %s " % filtersText.rstrip("AND ")
        # print command
        query(command)
        documento = fetchOne()

    else:
        command = """SELECT * FROM documentos """

        filtersText = ""
        if 'tipo' in filters.keys():
            command += """JOIN atributos ON (tipo_id=atributos.id) """
            filtersText += """UPPER(atributos.nombre)='%s' AND """ % filters.pop('tipo').upper()

        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command += "WHERE %s " % filtersText.rstrip("AND ")

        # print command

        query(command)
        documento = fetchOne()

    return documento


def getDocuments(**data):
    query("""SELECT documentos.* FROM operaciones_documentos JOIN documentos USING (documento_id) WHERE %s""" % filtersText(**data))
    dbDocuments = fetchAll()

    #~ documents = []
    #~ for dbDocument in dbDocuments:
        #~ document = Document(**dbDocument)
        #~ documents.append(document)

    return dbDocuments



def getDocumentos(**filters):
    if 'operacion_id' in filters.keys():
        command = """SELECT * FROM documentos JOIN operaciones_documentos USING (documento_id) """
        filtersText = ""
        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)
            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command += "WHERE %s " % filtersText.rstrip("AND ")

        query(command)
        documentos = fetchAll()
    else:
        documentos = getAll('documentos', **filters)
    return documentos


def getPlaces(**filters):
    filtersBody = "x['grupo']=='place' and "
    for filter in filters:
        filtersBody += "x['%s']==%s and " % (filter, filters[filter])
    exec("""data = [x for x in __local.attributes if %s]""" % filtersBody.rstrip('and '))
    return data


def getOperaciones(**filters):
    command = "SELECT * FROM operaciones "
    filtersText = ""

    if 'proceso_id' in filters.keys():
        procesoId = filters.pop('proceso_id')
        command = "SELECT operaciones.* FROM operaciones JOIN proceso_operacion USING (operacion_id) "
        filtersText += """proceso_operacion.proceso_id=%s AND """ % procesoId

    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "%s=%s AND " % (filter, filters[filter])

    if filtersText:
        command += "WHERE %s " % filtersText.rstrip("AND ")

    query(command)
    operaciones = fetchAll()

    return operaciones


def getOperations(**data):
    if 'documents' in data.keys():
        documentsData = data.pop('documents')

    query("""SELECT operaciones.* FROM proceso_operacion JOIN operaciones USING (operacion_id) WHERE %s""" % filtersText(**data))
    dbOperations = fetchAll()

    operations = []

    for dbOperation in dbOperations:
        operation = Operation(**dbOperation)
        operations.append(operation)

    return operations


def getPartidasDocumento(**filters):
    command = """SELECT * FROM partidasdocumento """
    filtersText = ""
    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        else:
            filtersText += "%s=%s AND " % (filter, filters[filter])
    if filtersText:
        command += "WHERE %s " % filtersText.rstrip("AND ")

    # print command
    query(command)
    partidasDocumento = fetchAll()
    # print partidasDocumento
    return partidasDocumento


def getPerson(**data):              # print "baseModel.getPerson()"
    """ Factory
        Receives data to load or create a valid Person Class Instance and database reco-*rd.
            Rol data optional which may contain optional Address data and Contact data
        Gives the Person Class Instance
    """

    ## Searches first On Memory Data
    #!
    #! Here goes On Memory Data search, This gets the complete Instance (includes rols)
    #!

    ## If not found, searches database
    dbPerson = None
    rolesData = None

    ## Isolate personData (remove rols data)
    rolsData = []
    if 'roles' in data.keys():
        rolsData = data.pop('roles')

    if 'entidad_id' in data.keys():
        ## search by id
        dbPerson = getOne('entidades', id=data.pop('entidad_id'))

    else:
        ## search by keys
        filters = {}
        if 'personalidad' in data.keys(): filters['personalidad'] = data['personalidad']
        if 'nombre' in data.keys():     filters['nombre'] = data['nombre']
        if 'nombre2' in data.keys():    filters['nombre2'] = data['nombre2']
        if 'rfc' in data.keys():        filters['rfc'] = data['rfc']
        if 'alias' in data.keys():      filters['alias'] = data['alias']

        dbPerson = getOne('entidades', **filters)

        if not dbPerson:
            ## Not found, create
            dbPerson = add('entidades', **data)

    if dbPerson:
        person = Person(**dbPerson)

    ## Add extra data
    if rolsData:
        person.setData(rols=rolsData)

    return person


def getProcess(**filters):
    print 'baseModel.getProcess()'
    if 'proceso_id' in filters.keys():
        ## Search by id
        dbProcess = getOne('procesos', proceso_id=filters.pop('proceso_id'))

    else:
        ## Search by keys
        command = """SELECT * FROM procesos JOIN proceso_operacion USING (proceso_id) JOIN operaciones_documentos USING (operacion_id) """
        filtersText = ""

        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)

            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command += "WHERE %s " % filtersText.rstrip("AND ")

        query(command)
        dbProcess = fetchOne()

    #~ if dbProcess:
        #~ process = Process(**dbProcess)

    return dbProcess



    if 'documento_id' in filters.keys():
        pass
    else:
        proceso = getOne('procesos', **filters)

    return proceso


def getFullProcess(**filters):
    print "baseModel.getFullProcess()", filters

    #~ if 'proceso_id' in filters.keys():
        #~ ## Search by id
        #~ dbProcess = getOne('procesos', proceso_id=filters.pop('proceso_id'))

    #~ else:
    if True:
        ## Search by keys
        command = """SELECT procesos.*, array_agg(ops.operacion_id) AS ops_id, array_agg(ops.operacion_tipo) AS ops_kind, array_agg(ops.cuenta) AS ops_cuenta, array_agg(ops.fecha) AS ops_fecha, array_agg(ops.monto) AS ops_monto, array_agg(ops.status) AS ops_status, array_agg(ops.referencia) AS ops_referencia, array_agg(ops.comentarios) AS ops_comentarios
FROM procesos
	JOIN proceso_operacion USING (proceso_id)
	JOIN operaciones AS ops USING(operacion_id)
	LEFT JOIN operaciones_documentos USING (operacion_id)
WHERE
GROUP BY proceso_id, tipo, procesos.fecha, procesos.status, reference """

        filtersText = ""

        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)

            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command = command.replace("WHERE", "WHERE %s " % filtersText.rstrip("AND "))
        else:
            command = command.replace("WHERE", "")

        #~ print "\n", command

        query(command)
        dbProcess = fetchOne()

        #~ print dbProcess

        operations = [{'operacion_id':id, 'operacion_tipo':dbProcess['ops_kind'][index], 'cuenta':dbProcess['ops_cuenta'], 'fecha':dbProcess['ops_fecha'][index], 'monto':dbProcess['ops_monto'][index], 'status':dbProcess['ops_status'][index], 'referencia':dbProcess['ops_referencia'][index], 'comentarios':dbProcess['ops_comentarios'][index]} for index, id in enumerate(dbProcess['ops_id'])]
        dbProcess.pop('ops_id')
        dbProcess.pop('ops_kind')
        dbProcess.pop('ops_cuenta')
        dbProcess.pop('ops_fecha')
        dbProcess.pop('ops_monto')
        dbProcess.pop('ops_status')
        dbProcess.pop('ops_referencia')
        dbProcess.pop('ops_comentarios')
        dbProcess['operations'] = operations

        #~ print dbProcess

    #~ if dbProcess:
        #~ process = Process(**dbProcess)

    return dbProcess



    if 'documento_id' in filters.keys():
        pass
    else:
        proceso = getOne('procesos', **filters)

    return proceso


def getProcesses(**filters):
    if 'rol_id' in filters.keys() or 'operacion_id' in filters.keys() or 'documentos.tipo_id' in filters.keys():
        command = """SELECT * FROM procesos JOIN proceso_operacion USING (proceso_id) JOIN operaciones USING (operacion_id) LEFT JOIN operaciones_documentos USING (operacion_id) LEFT JOIN documentos USING (documento_id) """

        filtersText = ""
        if 'operacion_id' in filters.keys():
            filters['proceso_operacion.operacion_id'] = filters.pop('operacion_id')

        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)
            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command += "WHERE %s " % filtersText.rstrip("AND ")

        #~ print '\n', command

        query(command)
        procesos = fetchAll()
    else:
        procesos = getAll('procesos', **filters)

    return procesos


def getFullProcesses(**filters):
    #~ if 'rol_id' in filters.keys() or 'operacion_id' in filters.keys() or 'documentos.tipo_id' in filters.keys():
        #~ command = """SELECT procesos.*, array_agg(ops2.*) AS operaciones
    #~ FROM procesos
    #~ JOIN proceso_operacion USING (proceso_id)
    #~ JOIN (
        #~ SELECT
            #~ ops.operacion_id, ops.cuenta,
            #~ array_agg(docs.*) AS documentoss
        #~ FROM operaciones AS ops
            #~ LEFT JOIN operaciones_documentos USING(operacion_id)
            #~ LEFT JOIN documentos AS docs USING (documento_id)
        #~ GROUP BY ops.operacion_id, ops.operacion_tipo, ops.cuenta, ops.fecha, ops.fecha2,
            #~ ops.monto, ops.status, ops.referencia, ops.comentarios
        #~ ) AS ops2 USING (operacion_id)
    #~ WHERE
    #~ GROUP BY proceso_id, tipo, fecha, status, reference"""

        command = """
SELECT procesos.*, array_agg(ops.operacion_id) AS ops_id, array_agg(ops.operacion_tipo) AS ops_kind, array_agg(ops.cuenta) AS ops_cuenta, array_agg(ops.fecha) AS ops_fecha, array_agg(ops.monto) AS ops_monto, array_agg(ops.status) AS ops_status, array_agg(ops.referencia) AS ops_referencia, array_agg(ops.comentarios) AS ops_comentarios, array_agg(documentos.documento_id) AS documentos_id

FROM procesos
JOIN
	(SELECT proceso_id
	FROM procesos
	LEFT JOIN proceso_operacion USING (proceso_id)
	LEFT JOIN operaciones USING (operacion_id)
	LEFT JOIN operaciones_documentos USING (operacion_id)
	LEFT JOIN documentos USING (documento_id)
	WHERE
	GROUP BY procesos.proceso_id) AS procesosFiltered USING (proceso_id)
LEFT JOIN proceso_operacion USING (proceso_id)
LEFT JOIN operaciones AS ops USING (operacion_id)
LEFT JOIN operaciones_documentos USING (operacion_id)
LEFT JOIN documentos USING (documento_id)

GROUP BY procesos.proceso_id, procesos.tipo, procesos.fecha, procesos.status, reference"""

        filtersText = ""
        if 'operacion_id' in filters.keys():
            filters['proceso_operacion.operacion_id'] = filters.pop('operacion_id')

        for filter in filters.keys():
            if type(filters[filter]) in (str, unicode, datetime.datetime):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)
            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText:
            command = command.replace("WHERE", "WHERE %s " % filtersText.rstrip("AND "))
        else:
            command = command.replace("WHERE", "")

        query(command)

        processes = fetchAll()

        for process in processes:

            operations = [{'operacion_id':id, 'operacion_tipo':process['ops_kind'][index], 'cuenta':process['ops_cuenta'], 'fecha':process['ops_fecha'][index], 'monto':process['ops_monto'][index], 'status':process['ops_status'][index], 'referencia':process['ops_referencia'][index], 'comentarios':process['ops_comentarios'][index]} for index, id in enumerate(process['ops_id'])]
            process.pop('ops_id')
            process.pop('ops_kind')
            process.pop('ops_cuenta')
            process.pop('ops_fecha')
            process.pop('ops_monto')
            process.pop('ops_status')
            process.pop('ops_referencia')
            process.pop('ops_comentarios')
            process['operations'] = operations

            #~ operations = [{'operacion_id':id, 'operacion_tipo':process['ops_kind'][index], 'cuenta':process['ops_cuenta'], 'fecha':process['ops_fecha'][index], 'monto':process['ops_monto'][index], 'status':process['ops_status'][index], 'referencia':process['ops_referencia'][index]} for index, id in enumerate(process['ops_id'])]
            #~ process.pop('ops_id')
            #~ process.pop('ops_kind')
            #~ process.pop('ops_cuenta')
            #~ process.pop('ops_fecha')
            #~ process.pop('ops_monto')
            #~ process.pop('ops_status')
            #~ process.pop('ops_referencia')
            #~ process['operations'] = operations

        return processes


def getProduct(**filters):
    product = getOne('products', **filters)
    return product


def getRecovery(mode=None):
    try:
        if not mode:
            question = getAttribute(category=u'system', name=u'question')['value']
            answer = getAttribute(category=u'system', name=u'answer')['value']
            return question, answer
        else:
            question = getAttribute(category=u'system', name=u'question2')['value']
            answer = getAttribute(category=u'system', name=u'answer2')['value']
        return question, answer
    except:
        return None, None


def getRol(**filters):
    """ Extrae desde la base de datos, el rol que cumple con los filtros
    Returns a list of dicts
    """
    # print "baseModel.getRol(%s)" % filters

    #! Parece que tipo solo busca por cliente

    command = """SELECT roles.*, e.personalidad, e.nombre, e.nombre2, e.alias, e.fechanacimiento, e.rfc, e.curp FROM roles JOIN entidades AS e ON (roles.entidad_id=e.id) """

    orderText = ""
    if 'order' in filters.keys():
        orderText = filters.pop('order')

    filtersText = ""
    if 'tipo' in filters.keys():
        filters['tipo_id'] = getAttribute(grupo=u'tipoRol', nombre=filters.pop('tipo'))['id']

    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "roles.%s=%s AND " % (filter, filters[filter])

    if filtersText:
        command += "WHERE %s " % filtersText.rstrip("AND ")
    if orderText:
        command += "ORDER BY %s" % orderText

    query(command)

    rol = fetchOne()

    return rol


def getRoles(**filters):
    """ Extrae desde la base de datos, los roles que cumplen con los filtros
    Returns a list of dicts
    """
    # print "baseModel.getRoles(%s)" % filters

    #! Parece que tipo solo busca por cliente

    command = """SELECT roles.*, e.personalidad, e.nombre, e.nombre2, e.alias, e.fechanacimiento, e.rfc, e.curp FROM roles JOIN entidades AS e ON (roles.entidad_id=e.id) """

    orderText = ""
    if 'order' in filters.keys():
        orderText = filters.pop('order')

    filtersText = ""
    if 'tipo' in filters.keys():
        filters['tipo_id'] = getAttribute(grupo=u'tipoRol', nombre=filters.pop('tipo'))['id']

    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "%s=%s AND " % (filter, filters[filter])

    if filtersText:
        command += "WHERE %s " % filtersText.rstrip("AND ")
    if orderText:
        command += "ORDER BY %s" % orderText

    query(command)

    roles = fetchAll()

    return roles


def getFullRol(**filters):
    """ Extrae desde la base de datos, el rol que cumple con los filtros
    Agrega los datos que pertinentes que se encuentran en otras tablas
    Returns a list of dicts
    """
    # print "baseModel.getRol(%s)" % filters

    #! Parece que tipo solo busca por cliente

    command = """SELECT roles.*, array_agg(attr.id) AS attributes_id, array_agg(attr.nombre) AS attributes_nombre, array_agg(attr.valor) AS attributes_valor, array_agg(attr.referencia) AS attributes_referencia,
    array_agg(addr.id) as addresses_id, array_agg(places.name) AS addresses_place_name, array_agg(places.id) AS addresses_place_id, array_agg(addr.areapostal) AS addresses_areapostal, array_agg(addr.areanominal) AS addresses_areanominal, array_agg(addr.calle) AS addresses_calle,
    ent.personalidad, ent.nombre, ent.nombre2, ent.alias, ent.fechanacimiento, ent.rfc, ent.curp
    FROM roles JOIN entidades AS ent ON (roles.entidad_id=ent.id) LEFT JOIN atributosrol AS attr ON (roles.id=attr.rol_id) JOIN addresses AS addr ON (roles.id=addr.rol_id) JOIN attributes AS places ON (addr.place_id=places.id)
    WHERE
    GROUP BY roles.id, roles.code, roles.entidad_id, roles.tipo_id, roles.funcion, roles.comentarios, roles.dato1, roles.dato2, ent.personalidad, ent.nombre, ent.nombre2, ent.alias, ent.fechanacimiento, ent.rfc, ent.curp
    """

    orderText = ""
    if 'order' in filters.keys():
        orderText = filters.pop('order')

    filtersText = ""
    if 'tipo' in filters.keys():
        filters['tipo_id'] = getAttribute(grupo=u'tipoRol', nombre=filters.pop('tipo'))['id']

    for filter in filters.keys():
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "roles.%s=%s AND " % (filter, filters[filter])

    if filtersText:
        command = command.replace("WHERE", "WHERE %s " % filtersText.rstrip("AND "))
    else:
        command = command.replace("WHERE", "")

    if orderText:
        command += "ORDER BY %s" % orderText


    #~ print "\n", command

    query(command)

    rol = fetchOne()

    attributes = [{'id':id, 'nombre':rol['attributes_name'][index], 'value':rol['attributes_value'][index], 'reference':rol['attributes_reference'][index]} for index, id in enumerate(rol['attributes_id'])]
    rol.pop('attributes_id')
    rol.pop('attributes_name')
    rol.pop('attributes_valor')
    rol.pop('attributes_referencia')

    rol['cuentas'] = [attribute for attribute in attributes if attribute['nombre']==u'cuenta']
    rol['telefonos'] = [attribute for attribute in attributes if attribute['nombre']==u'teléfonoFijo']

    addresses = [{'id':id, 'place_id':rol['addresses_place_id'][index], 'place_name':rol['addresses_place'][index], 'areapostal':rol['addresses_areapostal'][index], 'areanominal':rol['addresses_areanominal'][index], 'calle':rol['addresses_calle'][index]} for index, id in enumerate(rol['addresses_id'])]
    rol.pop('addresses_id')
    rol.pop('addresses_place_id')
    rol.pop('addresses_place')
    rol.pop('addresses_areapostal')
    rol.pop('addresses_areanominal')
    rol.pop('addresses_calle')
    rol['addresses'] = addresses

    #~ print rol

    return rol


def getRolAttributes(**filters):
    data = getAll('atributosrol', **filters)
    return data


class AtributoRol(object):

    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)
        self.status = 'valid'
        return None

    def setData(self, useDefaults=False, **data):
        if 'id' in data.keys():         self.id = data.pop('id')
        if 'rol_id' in data.keys():     self.rol_id = data.pop('rol_id')
        if 'nombre' in data.keys():     self.nombre = data.pop('nombre')
        if 'valor' in data.keys():      self.valor = data.pop('valor')
        if 'referencia' in data.keys(): self.referencia = data.pop('referencia')
        if 'usuario' in data.keys():    self.usuario = data.pop('usuario')

        if data:
            print "Sobran atributos al crear instancia de AtributoRol:\n%s" % data
            return data


class Address(object):

    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)
        self.status = 'valid'
        return None

    def setData(self, useDefaults=False, **data):
        if 'id' in data.keys():         self.id = data.pop('id')
        if 'rol_id' in data.keys():     self.rol_id = data.pop('rol_id')
        if 'tipo' in data.keys():       self.tipo = data.pop('tipo')
        else:   self.tipo = u"personal"
        if 'lugar_id' in data.keys():   self.lugar_id = data.pop('lugar_id')
        if 'areanominal' in data.keys(): self.areanominal = data.pop('areanominal')
        if 'areapostal' in data.keys(): self.areapostal = data.pop('areapostal')
        if 'areatelefonica' in data.keys(): self.areatelefonia = data.pop('areatelefonia')
        else:   self.areatelefonica = u""
        if 'calle' in data.keys():      self.calle = data.pop('calle')

        if data:
            print "Sobran atributos al crear instancia de Address:\n%s" % data
            return data


class Rol(object):

    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)
        self.status = 'valid'
        return None

    def setData(self, useDefaults=False, **data):
        if 'id' in data.keys():         self.id = data.pop('id')
        if 'code' in data.keys():       self.code = data.pop('code')
        if 'person_id' in data.keys():  self.person_id = data.pop('person_id')
        if 'kind_id' in data.keys():    self.kind_id = data.pop('kind_id')
        if 'function' in data.keys():   self.function = data.pop('function')
        if 'comment' in data.keys():    self.comment = data.pop('comment')
        if 'data1' in data.keys():      self.data1 = data.pop('data1')
        if 'data2' in data.keys():      self.data2 = data.pop('data2')

        if 'addresses' in data.keys():
            addressesData = data.pop('addresses')
            self.addresses = []
            for addressData in addressesData:
                dbAddress = None
                if 'id' in addressData.keys():
                    dbAddress = getOne('addresses', id=addressData.pop('id'))
                else:
                    filters = {}
                    if self.id:                         filters['rol_id'] = self.id
                    if 'calle' in addressData.keys():   filters['calle'] = addressData['calle']
                    dbAddress = getOne('addresses', **filters)
                    if not dbAddress:
                        dbAddress = add('addresses', addressData)
                if dbAddress:
                    address = Address(dbAddress)

                self.addresses.append(address)

        if 'contacts' in data.keys():
            contactsData = data.pop('contacts')
            self.contacts = []
            for contactData in contactsData:
                dbContacts = None
            else:
                filters = {}
                if self.id:                         filters['rol_id'] = self.id
                if 'valor' in contactData.keys():   filters['valor'] = contactData['valor']
                dbContact = getOne('atributosrol', **filters)
                if not dbContact:
                    dbContact = add('atributosrol', contactData)
            if dbContact:
                contact = AtributoRol(dbContact)

            self.contacts.append(contact)

        if 'extras' in data.keys():
            extrasData = data.pop('extras')
            self.extras = []
            for extraData in extrasData:
                dbExtras = None
            else:
                filters = {}
                if self.id:                         filters['rol_id'] = self.id
                if 'valor' in extraData.keys():     filters['valor'] = extraData['valor']
                dbExtra = getOne('atributosrol', **filters)
                if not dbExtra:
                    dbExtra = add('atributosrol', extraData)
            if dbExtra:
                extra = AtributoRol(dbExtra)

            self.extras.append(extra)

        if data:
            print "Sobran atributos al crear instancia de Rol:\n%s" % data
            return data



class Person(object):
    """
        Receives valid person data
            id, personality, name, name2, alias, birthdate, rfc, curp
        Gives Instance
    """

    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)
        self.status = "valid"
        return None


    # def __repr__(self):
        # return ("<Person(id:%s, personality:%s, nombre:'%s', nombre2:'%s', alias:'%s', fechanacimiento:'%s', rfc:'%s, curp:'%s')>" % (self.id, self.personalidad, self.nombre, self.nombre2, self.alias, self.fechanacimiento, self.rfc, self.curp)).encode('utf8')


    def setData(self, useDefaults=False, **data):

        if 'id' in data.keys():         self.id = data.pop('id')
        if 'personality' in data.keys(): self.personality = data.pop('personality')
        if 'name' in data.keys():       self.name = data.pop('name')
        if 'name2' in data.keys():      self.name2 = data.pop('name2')
        if 'alias' in data.keys():      self.alias = data.pop('alias')
        else:                           self.alias = u""
        if 'birthdate' in data.keys():  self.birthdate = data.pop('birthdate')
        else:                           self.birthdate = datetime.date(1900,1,1)
        if 'rfc' in data.keys():        self.rfc = data.pop('rfc')
        if 'curp' in data.keys():       self.curp = data.pop('curp')
        else:                           self.curp = u""

        if 'rols' in data.keys():
            rolsData = data.pop('rols')
            self.rols = []
            for rolData in rolsData:
                dbRol = None
                addressesData = None

                if 'addresses' in rolData.keys():
                    addressesData = roldata.pop('addresses')

                if 'id' in rolData.keys():
                    dbRol = getOne('roles', id=rolData.pop('id'))
                else:
                    filters = {}
                    if self.id:                     filters['entidad_id'] = self.id
                    if 'tipo_id' in rolData.keys(): filters['tipo_id'] = rolData['tipo_id']
                    elif 'tipo' in rolData.keys():  filters['tipo_id'] = getAttribute(grupo=u'tipoRol', nombre=datos.pop('tipo')).id
                    if 'code' in rolData.keys():    filters['code'] = rolData['code']

                    dbRol = getOne('roles', **filters)

                    if not dbRol:
                        dbRol.add('roles', **rolData)

                if dbRol:
                    rol = Rol(**dbRol)

                if addressesData:
                    rol.setData(addresses=addressesData)

                self.roles.append(rol)

        if data:
            print "Sobran atributos al crear instancia de Person:\n%s" % data
            return data




class Document(object):
    """ status  [u'abierto', u'cerrado', u'cancelado']
    """
    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)

    # def __repr__(self):
        # return (u"<Documento(documento_id:%s, tipo_id:%s, folio:'%s', fecha:'%s', rol_id:%s, subtotal:%s, descuento:%s, descuentoporcentaje:%s, impuesto:%s, impuestoporcentaje:%s, total:%s, status:'%s', referencia:'%s', fechaalta:'%s')>" % (self.documento_id, self.tipo_id, self.folio, self.fecha, self.rol_id, self.subtotal, self.descuento, self.descuentoporcentaje, self.impuesto, self.impuestoporcentaje, self.total, self.status, self.referencia, self.fechaalta)).encode('utf8')

    def setComplemento(self, complemento):
        self.complemento = complemento

    def setData(self, useDefaults=False, **data):
        if 'documento_id' in data.keys(): self.documento_id = data.pop('documento_id')
        if 'tipo_id' in data.keys():    self.tipo_id = data.pop('tipo_id')
        if 'folio' in data.keys():      self.folio = data.pop('folio')
        if 'fecha' in data.keys():      self.fecha = data.pop('fecha')
        if 'rol_id' in data.keys():     self.rol_id = data.pop('rol_id')
        if 'subtotal' in data.keys():   self.subtotal = data.pop('subtotal')
        if 'descuento' in data.keys():  self.descuento = data.pop('descuento')
        if 'descuentoporcentaje' in data.keys(): self.descuentoporcentaje = data.pop('descuentoporcentaje')
        else:                           self.descuentoporcentaje = dec("0")
        if 'impuesto' in data.keys():   self.impuesto = data.pop('impuesto')
        if 'impuestoporcentaje' in data.keys(): self.impuestoporcentaje = data.pop('impuestoporcentaje')
        else:                           self.impuestoporcentaje = dec("0")
        if 'total' in data.keys():      self.total = data.pop('total')
        if 'status' in data.keys():     self.status = data.pop('status')
        else:                           self.status = u'abierto'
        if 'referencia' in data.keys(): self.referencia = data.pop('referencia')
        if 'fechaalta' in data.keys():  self.fechaalta = data.pop('fechaalta')
        else:                           self.fechaalta = datetime.datetime.today()
        if 'useDefaults' in data:       data.pop('useDefaults')

        if data:
            print "Sobran parametros en Documento.setData()", data


class Operation(object):
    """
        tipo    [u'solicitud']
        cuentas [u'caja', u'inventarios', u'pagos', u'cobros']
        status  [u'abierta', u'cerrada', u'cancelada']
    """

    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)

    # def __repr__(self):
        # return ("<Operacion (operacion_id:%s, operacion_tipo:'%s', cuenta:'%s', fecha:%s, fecha2:%s, monto:%s, status:'%s', referencia:'%s', comentarios:'%s') >" % (self.operacion_id, self.operacion_tipo, self.cuenta, self.fecha, self.fecha2, self.monto, self.status, self.referencia, self.comentarios)).encode('utf8')

    def setData(self, useDefaults=False, **data):
        if 'operacion_id' in data.keys(): self.operacion_id = data.pop('operacion_id')
        if 'operacion_tipo' in data.keys(): self.operacion_tipo = data.pop('operacion_tipo')
        if 'cuenta' in data.keys():     self.cuenta = data.pop('cuenta')
        if 'fecha' in data.keys():      self.fecha = data.pop('fecha')
        else:                           self.fecha = datetime.datetime.today()
        if 'fecha2' in data.keys():     self.fecha2 = data.pop('fecha2')
        if 'monto' in data.keys():      self.monto = data.pop('monto')
        if 'status' in data.keys():     self.status = data.pop('status')
        if 'referencia' in data.keys(): self.referencia = data.pop('referencia')
        else: self.referencia = u""
        if 'comentarios' in data.keys(): self.comentarios = data.pop('comentarios')
        else: self.comentarios = u""

        if 'documents' in data.keys():
            documentsData = data.pop('documents')
            self.documents = []
            for documentData in documentsData:
                dbDocument = None
                if 'id' in documentData.keys():
                    dbDocument = getOne('documentos', id=documentData.pop('id'))
                else:
                    filters = {}
                    if self.id:                         filters['rol_id'] = self.id
                    if 'calle' in documentData.keys():  filters['calle'] = documentData['calle']
                    dbDocument = getOne('documentos', **filters)
                    if not dbDocument:
                        dbAddress = add('documents', documentData)
                if dbDocument:
                    document = Document(dbDocument)

                self.documents.append(document)
        else:
            self.documents = getDocuments(operacion_id=self.operacion_id)





class Process(object):
    """
        status  [u'abierto', u'cerrado', 'cancelado']
    """
    def __init__(self, **kwds):
        self.setData(useDefaults=True, **kwds)

    # def __repr__(self):
        # return "<Proceso(proceso_id:%s, tipo:'%s', fecha:'%s', status:'%s')>" % (self.proceso_id, self.tipo, self.fecha, self.status)

    def setData(self, useDefaults=False, **data):
        if 'proceso_id' in data.keys(): self.proceso_id = data.pop('proceso_id')
        if 'tipo' in data.keys():   self.tipo = data.pop('tipo')
        if 'fecha' in data.keys():  self.fecha = data.pop('fecha')
        else:                       self.fecha = datetime.datetime.today()
        if 'status' in data.keys(): self.status = data.pop('status')

        if 'operations' in data.keys():
            operationsData = data.pop('operations')
            self.operations = []
            for operationData in operationsData:
                dbOperation = None
                documentsData = None

                if 'documents' in operationData.keys():
                    documentsData = operationData.pop('documents')

                if 'id' in operationData.keys():
                    dbOperation = getOne('operations', id=operationData.pop('id'))
                else:
                    f=g
                    # filters = {}
                    # if self.id:                     filters['entidad_id'] = self.id
                    # if 'tipo_id' in rolData.keys(): filters['tipo_id'] = rolData['tipo_id']
                    # elif 'tipo' in rolData.keys():  filters['tipo_id'] = getAttribute(grupo=u'tipoRol', nombre=datos.pop('tipo')).id
                    # if 'code' in rolData.keys():    filters['code'] = rolData['code']

                    # dbRol = getOne('roles', **filters)

                    # if not dbRol:
                        # dbRol.add('roles', **rolData)

                if dbOperation:
                    operation = Operation(**dbOperation)

                if documentsData:
                    operation.setData(documents=documentsData)

                self.operations.append(operation)
        else:
            self.operations = getOperations(proceso_id=self.proceso_id)

        if data:
            print "Sobran atributos al crear instancia de Process:\n%s" % data
            return data


class Pago(object):

    def __init__(self, *args, **kwds):
        self.setData(useDefaults=True, **kwds)

    def setData(self, useDefaults=False, **data):
        if 'id' in data.keys():         self.id = kwds['id']
        if 'documento_id' in data.keys(): self.documento_id = kwds.pop('documento_id')
        if 'tipo_id' in data.keys():    self.tipo_id = unicode(kwds.pop('tipo_id'))
        if 'fecha' in data.keys():      self.fecha = kwds.pop('fecha')
        if 'monto' in data.keys():      self.monto = kwds.pop('monto')
        if 'referencia' in data.keys(): self.referencia = kwds.pop('referencia')
        if 'dato1' in data.keys():      self.dato1 = kwds.pop('dato1')

    #~ def __repr__(self):
        #~ return (u"<Pago (id:%s, documento_id:%s, tipo_id:%s, fecha:%s, monto:%s, referencia:%s, dato1:%s)>" % (self.id, self.documento_id, self.tipo_id, self.fecha, self.monto, self.referencia, self.dato1)).encode('utf-8')




def dbConnect(u='', p='', h='', d='bm'):
    try:
        if not h:

            h = session.config.pull('properties', 'server')

        string = "host='%s' user='%s' password='%s' dbname='%s'" % (h, u, p, d)

        __local.conn = adapter.connect(string.rstrip(", "))

        #~ getFullProcesses(proceso_id=1117)

        return '00000', ''

    except adapter.OperationalError, e:
        message = e.args[0].decode('utf8')

        # print message.encode('utf8')

        if 'too many conn' in message:
            return '53300', message[:message.find('for')]
        elif 'traducir' in message:
            return '08001', u"No hay acceso a %s" % message[message.find(u'«')+1:message.find(u'»')]
        elif 'no existe' in message:
            return '20400', u"No encontré la base de datos en %s" % h
        elif 'rechazada' in message:
            return '08004', u"No hay acceso a %s" % message[message.find(u'«')+1:message.find(u'»')]
        elif "to server"  in str(sys.exc_info()[1]):
            ## Could not connect to server: Connection refused
            return '08011', u"No hay acceso a %s" % message[message.find(u'"')+1:message.rfind(u'"')]
        else:
            return '99999', message


    except Exception, e:

        print sys.exc_info()
        print 0, dir(sys.exc_info()[0])
        print 1, dir(sys.exc_info()[1])
        print 2, dir(sys.exc_info()[2])

        # print 11, sys.exc_info()[1].pgerror

        if "database" in str(sys.exc_info()[1]):
            return 3011                         # La base de datos no existe
        elif "user" in str(sys.exc_info()[1]) or "role" in str(sys.exc_info()[1]):
            return 2025                         # Combinación de usuario y contraseña no válida
        elif "password" in str(sys.exc_info()[1]):
            return 2025                         # Combinación de usuario y contraseña no válida
        elif "nknown host" in str(sys.exc_info()[1]):
            return 2040                         # Servidor desconocido
        elif "many clients" in str(sys.exc_info()[1]):
            return 2045                         # Se alcanzó el límite de conexiones
        elif "refused" in str(sys.exc_info()[1]):
            return 2048                         # No encontré el servidor
        elif "server closed the connection unexpectedly" in str(sys.exc_info()[1]):
            return 2043                         # El servidor canceló la conexión sin causa aparente
        elif "could not connect to server"  in str(sys.exc_info()[1]):
            return 2048                         # No encontré el servidor
        elif "criptidosdigitales" in str(sys.exc_info()[1]):
            return 9999
        else:
            print "    %s" % sys.exc_info()[1]
            return 9999


def execute(command):
    openCursor()
    try:
        __local.cursor.execute(command)
    except:
        print "\nERROR @ baseModel.execute()"
        print "command:\n    ", command
        print sys.exc_info()
    __local.cursor.close()
    __local.conn.commit()


def fetchAll():
    result = __local.cursor.fetchall()
    if result:
        result = [dict(x) for x in result]
    __local.cursor.close()
    return result


def fetchOne():
    result = __local.cursor.fetchone()
    if result:
        result = dict(result)
    __local.cursor.close()
    return result


def getAll(tables, **filters):
    if 'order' in filters.keys():
        orderText = filters.pop('order')
    else:
        orderText = ""
    try:
        command = "SELECT * FROM %s " % tables

        filtersText = ""
        for filter in filters:
            if filter == "#":
                 filtersText += "%s AND " % (filters[filter])
            elif ' in' in filter:
                filtersText += ("%s%s AND " %
                    (filter, filters[filter])).replace('[', '(').replace(']', ')')
            elif type(filters[filter]) in [int]:
                filtersText += "%s=%s AND " % (filter, filters[filter])
            elif ' LIKE' in filter.upper():
                filtersText += "UPPER(%s) LIKE '%s%%' AND " % (filter.upper().rstrip('LIKE'),
                    filters[filter].upper())
            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s='%s' AND " % (filter, filters[filter])

        if filtersText: command += "WHERE %s " % filtersText.rstrip("AND ")

        if orderText: command += "ORDER BY %s " % orderText.rstrip(", ")

        cursor = __local.conn.cursor(cursor_factory=extras.DictCursor)
        cursor.execute(command)
        result = cursor.fetchall()
        cursor.close()
        if result:
            result = [dict(x) for x in result]
        return result
    except:
        print command
        print sys.exc_info()[1]
        print "Can't get %s " % tables
        # self.rollback()
        raise


def getOne(table, **filters):
    """ Crea el commando SQL para extraer un renglón a partir de los argumentos
    """

    try:
        command = "SELECT * FROM %s " % table
        filtersText = ""
        for filter in filters:

            if type(filters[filter]) in (str, unicode, datetime.datetime, datetime.date):
                filtersText += "%s='%s' AND " % (filter, filters[filter])
            elif type(filters[filter]) in (list,):
                temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
                filtersText += "%s='%s' AND " % (filter, temp)
            elif filters[filter] == None:
                filtersText += "%s is null AND " % filter
            else:
                filtersText += "%s=%s AND " % (filter, filters[filter])

        if filtersText: command += "WHERE %s " % filtersText.rstrip("AND ")

        cursor = __local.conn.cursor(cursor_factory=extras.DictCursor)

        # print command.encode('utf8')

        cursor.execute(command)
        result = cursor.fetchone()
        cursor.close()
        if result:
            result = dict(result)
        else:
            print command.encode('utf8')
        return result

    except Exception, e:
        # print e.pgcode

        print"%s" % sys.exc_info()[1]
        print command.encode('utf8')
        # self.rollback()
        raise


def filtersText(**filters):
    filtersText = ""
    for filter in filters:
        if type(filters[filter]) in (str, unicode, datetime.datetime):
            filtersText += "%s='%s' AND " % (filter, filters[filter])
        elif type(filters[filter]) in (list,):
            temp = ("%s" % filters[filter]).replace("[", "{").replace("]","}").replace("'", '"')
            filtersText += "%s='%s' AND " % (filter, temp)
        elif filters[filter] == None:
            filtersText += "%s is null AND " % filter
        else:
            filtersText += "%s=%s AND " % (filter, filters[filter])
    return filtersText.rstrip("AND ")


def openCursor():
    # print "\nmainModel.openCursor()", __local.cursor
    try:
        if __local.cursor != None:
            if not __local.cursor.closed:
                return
        __local.cursor = __local.conn.cursor(cursor_factory=extras.DictCursor)
    except:
        print sys.exc_info()
        print sys.exc_info()[0]
        print sys.exc_info()[1]
        print sys.exc_info()[2]
        raise


def query(command):
    try:
        openCursor()
        __local.cursor.execute(command)
    except:
        ## Here can be catched a conexion error
        print 11, sys.exc_info()
        print "command:\n    %s" % command
        raise




def set(table, **data):
    """
    """
    if 'id' in data.keys():
        idKey = 'id'
    else:
        idKey = [x for x in data.keys() if x[-3:]=='_id' and x[:2]==table[:2] and '_' not in table]
        if idKey:
            idKey = idKey[0]

    if idKey:
        id = data.pop(idKey)
        dbData = getOne(table, **{idKey:id})
    else:
        dbData = None

    if dbData:
        command = """UPDATE %s SET """ % table
        pairsText = ""
        for key in data.keys():
            if type(data[key]) in [str, unicode, datetime.datetime, datetime.date, Decimal]:
                pairsText += "%s='%s', " % (key, data[key])
            elif type(data[key]) in [int]:
                pairsText += "%s=%s, " % (key, data[key])
        command += "%s WHERE %s=%s " % (pairsText.rstrip(', '), idKey, id)

        #~ print command.encode('utf8')

        execute(command)

    else:
        command = """INSERT INTO %s """ % table
        fieldsText = ""
        valuesText = ""
        for key in data.keys():
            if type(data[key]) in [str, unicode, datetime.datetime, datetime.date]:
                fieldsText += "%s, " % key
                valuesText += "'%s', " % data[key]
            elif data[key] == None:
                fieldsText += "%s, " % key
                valuesText += "Null, "
            else:
                fieldsText += "%s, " % key
                valuesText += "%s, " % data[key]
        command = "%s (%s) VALUES (%s)" % (command, fieldsText.rstrip(", "), valuesText.rstrip(", "))

        #~ print command.encode('utf8')

        execute(command)

    if idKey:
        dbData = getOne(table, **{idKey:id})
    else:
        dbData = getOne(table, **data)

    # print dbData

    return dbData


def initDb(*args):
    print "\nbaseModel.initDb()"
    try:
        version = getOne('attributes', code=1103, category='sistema', name='versionBasedatos')['value']
    except:
        createDb()
        version = 1

    if version < 52:
        print "Inicializando Base de datos"
        """
            Se busca que algunos datos tengan una id fija para compatibilidad entre diferentes instancias del sistema

            1100-1199  Sistema
            1200-1399  Procesos, documentos, cuentas, pagos
            1400-1599  Entidades
            1600-1799  products
            1800-1999  Pedidos
            2000-2999  Lugares
            4000-4199  Equipos - Tipos
            4200-4499  Equipos - Marcas
            4500-4999  Equipos - Lineas
        """

        import os
        registros = [
            {'code':1103, 'category':u'sistema', 'name':u'versionBasedatos', 'valor':'0'},
            {'code':1105, 'category':u'sistema', 'name':u'ruta', 'valor':u'%s' % os.getcwd()},
            {'code':1107, 'category':u'sistema', 'name':u'rutaDocumentos', 'valor':u'documentos'},
            {'code':1109, 'category':u'sistema', 'name':u'contraseña', 'valor':u'firstRun', 'reference':u'1'},
            {'code':1111, 'category':u'sistema', 'name':u'contraseña2', 'valor':u'1'},
            {'code':1113, 'category':u'sistema', 'name':u'contrasenaHerramientas'},
            {'code':1115, 'category':u'sistema', 'name':u'contrasenaMantenimiento'},
            {'code':1117, 'category':u'sistema', 'name':u'proteccionF10'},
            {'code':1121, 'category':u'system', 'name':u'runCount', 'valor':u'0'},
            {'code':1125, 'category':u'system', 'name':u'useUsername', 'valor':u'1'},
            {'code':1145, 'category':u'sistema', 'name':u'mostrarTips', 'valor':u'1'},
            {'code':1147, 'category':u'sistema', 'name':u'tipDefault', 'valor':u'0'},
            {'code':1151, 'category':u'sistema', 'name':u'impuestoGeneral', 'valor':u'16.00'},

            {'code':1191, 'category':u'tipoContacto', 'name':u'personal'},
            {'code':1193, 'category':u'tipoContacto', 'name':u'trabajo'},

            {'code':1205, 'category':u'procesos', 'name':u'recalcularPreciosAlComprar', 'valor':u'0'},

            {'code':1231, 'category':u'statusProcesos', 'name':u'abierto'},
            {'code':1233, 'category':u'statusProcesos', 'name':u'cerrado'},
            {'code':1235, 'category':u'statusProcesos', 'name':u'cancelado'},

            {'code':1251, 'category':u'tipoDocumento', 'name':u'pedido', 'reference':u'pedido'},
            {'code':1253, 'category':u'tipoDocumento', 'name':u'remisión', 'valor':u'control', 'reference':u'compra, venta, inventarios, caja'},
            {'code':1255, 'category':u'tipoDocumento', 'name':u'factura', 'valor':u'fiscal', 'reference':u'compra, venta, inventarios, caja'},
            {'code':1257, 'category':u'tipoDocumento', 'name':u'nota de crédito', 'reference':u'caja'},
            {'code':1259, 'category':u'tipoDocumento', 'name':u'pagaré', 'reference':u'cobro, pago, caja'},
            {'code':1261, 'category':u'tipoDocumento', 'name':u'cheque', 'reference':u'cobro, pago, caja'},
            {'code':1263, 'category':u'tipoDocumento', 'name':u'efectivo', 'reference':u'cobro, pago, caja'},
            {'code':1265, 'category':u'tipoDocumento', 'name':u'servicio'},

            # {'code':1267, 'category':u'tipoDocumentoCompra', 'name':u'default', 'valor': }
            # {'code':1269, 'category':u'tipoDocumentoVenta', 'name':u'default', 'valor': }

            {'code':1271, 'category':u'statusDocumento', 'name':u'Pendiente'},
            {'code':1273, 'category':u'statusDocumento', 'name':u'Procesada'},

            {'code':1281, 'category':u'folioDocumento', 'name':u'compra', 'valor':u'0'},
            {'code':1283, 'category':u'folioDocumento', 'name':u'ventaFiscal', 'valor':u'0'},
            {'code':1285, 'category':u'folioDocumento', 'name':u'ventaControl', 'valor':u'0'},
            {'code':1287, 'category':u'folioDocumento', 'name':u'pedido', 'valor':u'0'},
            {'code':1289, 'category':u'folioDocumento', 'name':u'servicio', 'valor':u'0'},
            {'code':1291, 'category':u'folioDocumento', 'name':u'presupuesto', 'valor':u'0'},
            {'code':1293, 'category':u'folioDocumento', 'name':u'pagaré', 'valor':u'0'},

            {'code':1305, 'category':u'cuentas', 'name':u'caja', 'reference':u'1001'},
            {'code':1307, 'category':u'cuentas', 'name':u'inventarios', 'reference':u'1002'},
            {'code':1309, 'category':u'cuentas', 'name':u'pagos', 'reference':u'1003'},
            {'code':1311, 'category':u'cuentas', 'name':u'cobros', 'reference':u'1004'},
            {'code':1313, 'category':u'cuentas', 'name':u'pasivos', 'reference':u'1005'},

            {'code':1321, 'category':u'formaPago', 'name':u'efectivo', 'reference':u'1'},
            {'code':1323, 'category':u'formaPago', 'name':u'cheque', 'reference':u'2'},
            {'code':1325, 'category':u'formaPago', 'name':u'crédito', 'reference':u'3'},

            {'code':1331, 'category':u'condicionesPago', 'name':u"Anticipado", 'valor':u'0'},
            {'code':1333, 'category':u'condicionesPago', 'name':u"Al recibir", 'valor':u'1'},
            {'code':1335, 'category':u'condicionesPago', 'name':u"30 días", 'valor':u'30'},
            {'code':1337, 'category':u'condicionesPago', 'name':u'15 días', 'valor':u'15'},
            {'code':1339, 'category':u'condicionesPago', 'name':u"10 días", 'valor':u'10'},

            # {'code':2000, 'category':u'lugar', 'name':u''},
            {'code':2009, 'category':u'lugar', 'name':u'Acapulco, Gro.'},
            {'code':2017, 'category':u'lugar', 'name':u'Aguascalientes, Ags.'},
            {'code':2025, 'category':u'lugar', 'name':u'Campeche, Camp.'},
            {'code':2033, 'category':u'lugar', 'name':u'Cd. Juarez, Chih.'},
            {'code':2041, 'category':u'lugar', 'name':u'Cd. Obregón, Son.'},
            {'code':2049, 'category':u'lugar', 'name':u'Cd. Victoria, Tamps.'},
            {'code':2057, 'category':u'lugar', 'name':u'Chetumal, QRoo.'},
            {'code':2065, 'category':u'lugar', 'name':u'Chihuahua, Chih.'},
            {'code':2073, 'category':u'lugar', 'name':u'Chilpancingo, Gro.'},
            {'code':2081, 'category':u'lugar', 'name':u'Colima, Col.'},
            {'code':2089, 'category':u'lugar', 'name':u'Culiacán, Sin.'},
            {'code':2097, 'category':u'lugar', 'name':u'Cuernavaca, Mor.'},
            {'code':2105, 'category':u'lugar', 'name':u'Durango, Dgo.'},
            {'code':2113, 'category':u'lugar', 'name':u'Guadalajara, Jal.'},
            {'code':2121, 'category':u'lugar', 'name':u'Guanajuato, Gto.'},
            {'code':2129, 'category':u'lugar', 'name':u'Guasave, Sin.'},
            {'code':2137, 'category':u'lugar', 'name':u'Hermosillo, Son.'},
            {'code':2145, 'category':u'lugar', 'name':u'Jalapa, Ver.'},
            {'code':2153, 'category':u'lugar', 'name':u'La Paz, B.C.S.'},
            {'code':2161, 'category':u'lugar', 'name':u'Los Mochis, Sin.', 'valor':u'default'},
            {'code':2169, 'category':u'lugar', 'name':u'Mazatlán, Sin.'},
            {'code':2177, 'category':u'lugar', 'name':u'Mérida, Yuc.'},
            {'code':2185, 'category':u'lugar', 'name':u'Mexicali, B.C.'},
            {'code':2193, 'category':u'lugar', 'name':u'México, D.F.'},
            {'code':2201, 'category':u'lugar', 'name':u'Monterrey, N.L.'},
            {'code':2209, 'category':u'lugar', 'name':u'Morelia, Mich.'},
            {'code':2217, 'category':u'lugar', 'name':u'Navojoa, Son.'},
            {'code':2225, 'category':u'lugar', 'name':u'Nogales, Son.'},
            {'code':2233, 'category':u'lugar', 'name':u'Oaxaca, Oax.'},
            {'code':2241, 'category':u'lugar', 'name':u'Pachuca, Hgo.'},
            {'code':2249, 'category':u'lugar', 'name':u'Puebla, Pue.'},
            {'code':2257, 'category':u'lugar', 'name':u'Queretaro, Qro.'},
            {'code':2265, 'category':u'lugar', 'name':u'Saltillo, Coah.'},
            {'code':2273, 'category':u'lugar', 'name':u'San Luis Potosí, S.L.P.'},
            {'code':2281, 'category':u'lugar', 'name':u'Tepic, Nay.'},
            {'code':2289, 'category':u'lugar', 'name':u'Tijuana, B.C.'},
            {'code':2297, 'category':u'lugar', 'name':u'Tlaxcala, Tlax.'},
            {'code':2305, 'category':u'lugar', 'name':u'Toluca, Edo.Mex.'},
            {'code':2313, 'category':u'lugar', 'name':u'Tuxtla Gutierrez, Chis.'},
            {'code':2321, 'category':u'lugar', 'name':u'Villahermosa, Tab.'},
            {'code':2329, 'category':u'lugar', 'name':u'Veracruz, Ver.'},
            {'code':2337, 'category':u'lugar', 'name':u'Zacatecas, Zac.'},

            {'code':1, 'category':u'tipoEquipo', 'name':u'Sedán'},
            {'code':2, 'category':u'tipoEquipo', 'name':u'Familiar'},
            {'code':3, 'category':u'tipoEquipo', 'name':u'Monovolumen'},
            {'code':4, 'category':u'tipoEquipo', 'name':u'Furgoneta'},
            {'code':5, 'category':u'tipoEquipo', 'name':u'Utilitario'},
            {'code':6, 'category':u'tipoEquipo', 'name':u'Pickup'},
            {'code':7, 'category':u'tipoEquipo', 'name':u'Reparto'},

            {'code':3, 'category':u'marcaEquipo', 'name':u'BMW'},
            {'code':7, 'category':u'marcaEquipo', 'name':u'Cadillac'},
            {'code':11, 'category':u'marcaEquipo', 'name':u'Chevrolet'},
            {'code':15, 'category':u'marcaEquipo', 'name':u'Chrysler'},
            {'code':19, 'category':u'marcaEquipo', 'name':u'Ford'},
            {'code':23, 'category':u'marcaEquipo', 'name':u'Honda'},
            {'code':27, 'category':u'marcaEquipo', 'name':u'Hummer'},
            {'code':31, 'category':u'marcaEquipo', 'name':u'Hyundai'},
            {'code':35, 'category':u'marcaEquipo', 'name':u'Jeep'},
            {'code':39, 'category':u'marcaEquipo', 'name':u'Mazda'},
            {'code':43, 'category':u'marcaEquipo', 'name':u'Mercedes'},
            {'code':47, 'category':u'marcaEquipo', 'name':u'Mitsubishi'},
            {'code':51, 'category':u'marcaEquipo', 'name':u'Nissan'},
            {'code':55, 'category':u'marcaEquipo', 'name':u'Pontiac'},
            {'code':59, 'category':u'marcaEquipo', 'name':u'Renault'},
            {'code':63, 'category':u'marcaEquipo', 'name':u'Seat'},
            {'code':67, 'category':u'marcaEquipo', 'name':u'Toyota'},
            {'code':71, 'category':u'marcaEquipo', 'name':u'Volkswagen'},

            {'code':0, 'category':u'clasificacionProducto', 'name':u''}
        ]

        # id=1261, category=u'default', name=u'tipoDocumentoCompra', valor=
        # id=1263, category=u'default', name=u'tipoDocumentoVenta', valor=
        # id=2001, category=u'default', name=u'lugar', valor=

        for registro in registros:
            set('attributes', **registro)
            #~ atributoI(**registro)

        marca = getOne('attributes', category=u'marcaEquipo', name=u'Ford')

        registros = [
                {'code':3, 'category':u'lineaEquipo', 'name':u'Ikon', 'reference':u'%s' % marca['id']},
                {'code':7, 'category':u'lineaEquipo', 'name':u'Focus', 'reference':u'%s' % marca['id']},
                {'code':11, 'category':u'lineaEquipo', 'name':u'Mondeo', 'reference':u'%s' % marca['id']},
                {'code':15, 'category':u'lineaEquipo', 'name':u'Mustang', 'reference':u'%s' % marca['id']},
                {'code':19, 'category':u'lineaEquipo', 'name':u'F', 'reference':u'%s' % marca['id']},
                {'code':23, 'category':u'lineaEquipo', 'name':u'Navigator', 'reference':u'%s' % marca['id']}
                ]

        for registro in registros:
            set('attributes', **registro)

        tipoDocumento = getOne('attributes', category=u'tipoDocumento', name=u'factura')
        set('attributes', code=1267, category=u'tipoDocumentoCompra', name=u'default', valor=unicode(tipoDocumento['id']))

        tipoDocumento = getOne('attributes', category=u'tipoDocumento', name=u'remisión')
        set('attributes', code=1269, category=u'tipoDocumentoVenta', name=u'default', valor=unicode(tipoDocumento['id']))

        set('attributes', code=1103, category=u'sistema', name=u'versionBasedatos', valor=u"52")
        version = 52

    for arg in args:
        exec("from %s.import *")

    try:
        loadAttributes()
    except:
        print "/nbaseModel.initDb()/n    Could not load Attributes"

    # loadPriceRules()

    # print ") =========="

    return '00000'



def createDb():
    print "baseModel.createDb()"
    try:
        execute("""CREATE TABLE attributes (
            id      serial PRIMARY KEY,
            code    INTEGER,
            category TEXT,
            name    TEXT,
            value   TEXT,
            reference TEXT,
            status  TEXT,
            span    TIMESTAMP
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE attributeshistory (
            attribute_id    INTEGER,
            fieldname       VARCHAR,
            value           TEXT,
            span            TIMESTAMP
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE processes (
            id      serial PRIMARY KEY,
            kind    TEXT,
            date    TIMESTAMP,
            status  TEXT,
            span    TIMESTAMP,
            reference INTEGER REFERENCES processes (id)
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE operations (
            id      serial PRIMARY KEY,
            kind    TEXT,
            account TEXT,
            date    TIMESTAMP,
            date2   TIMESTAMP,
            amount  DECIMAL(12, 4),
            status  TEXT,
            reference TEXT,
            commentary TEXT,
            span    TIMESTAMP
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE documents (
            id          INTEGER PRIMARY KEY,
            kind_id     INTEGER REFERENCES attributes (id),
            number      TEXT,
            date        TIMESTAMP,
            rol_id      INTEGER REFERENCES roles,
            subtotal    DECIMAL(12, 4),
            discount    DECIMAL(12, 4),
            discountasfraction DECIMAL(6, 2),
            tax         DECIMAL(12, 4),
            taxasfraction DECIMAL (6, 2),
            total       DECIMAL (12, 4),
            status      TEXT,
            reference   TEXT,
            span        TIMESTAMP
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE documentitems (
            id          INTEGER PRIMARY KEY,
            document_id INTEGER REEFRENCES documents,
            subject_id  INTEGER,
            quantity    NUMERIC(6, 2),
            cost        NUMERIC(12, 4),
            price       NUMERIC(12, 4),
            discountF   NUMERIC(12, 4),
            taxF        NUMERIC(12, 2),
            net_price   NUMERIC(12, 4),
            status      TEXT
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE process2operation (
            process_id      INTEGER ON UPDATE CASCADE ON DELETE CASCADE,
            operation_id    INTEGER ON UPDATE NO ACTION ON DELETE NO ACTION
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE operation2document (
            operation_id INTEGER ON UPDATE CASCADE ON DELETE CASCADE,
            document_id  INTEGER ON UPDATE NO ACTION ON DELETE NO ACTION
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE payments (
            id              INTEGER PRIMARY KEY,
            document_id     INTEGER REFERENCES documents,
            kind_id         INTEGER REFERENCES attributes,
            date            TIMESTAMP,
            amount          NUMERIC (12, 4),
            reference       TEXT,
            data1           TEXT
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE addresses(
            id      SERIAL PRIMARY KEY,
            rol_id  INTEGER  REFERENCES roles,
            kind    TEXT,
            place_id    INTEGER REFERENCES attributes,
            nominalarea TEXT,
            postalarea  TEXT,
            telephonearea TEXT,
            street      TEXT,
            status      TEXT
        )""")
    except:
        pass

    try:
        execute("""CREATE TABLE services (
            id          INTEGER PRIMARY KEY,
            document_id INTEGER REFERENCES documents,
            equipment_id   INTEGER REFERENCES equipments,
            gauge1      TEXT,
            gauge2      TEXT,
            gauge3      TEXT,
            accesories  TEXT,
            observations TEXT,
            symptoms    TEXT,
            diagnosis   TEXT,
            diagnosisdate TIMESTAMP,
            technician_id INTEGER REFERENCES roles
        )""")
    except:
        pass

    #~ try:
        #~ execute("""CREATE TABLE classifications (
            #~ id          INTEGER PRIMARY KEY,
            #~ brand       TEXT,
            #~ line        TEXT,
            #~ subline     TEXT,
            #~ model       TEXT,
            #~ submodel    TEXT,
            #~ kind        TEXT,
            #~ subkind     TEXT
        #~ )""")
    #~ except:
        #~ pass

    #~ try:
        #~ execute("""CREATE TABLE equipment (
            #~ id          INTEGER PRIMARY KEY,
            #~ owner_id    INTEGER REFERENCES roles,
            #~ operator_id INTEGER REFERENCES roles,
            #~ serial      TEXT,
            #~ registry    TEXT,
            #~ code        TEXT,
            #~ kind_id     INTEGER REFERENCES attributes,
            #~ classificatin_id INTEGER REFEERNCES classifications,
            #~ color       TEXT,
            #~ observations TEXT
        #~ )""")
    #~ except:
        #~ pass

    try:
        execute("""CREATE TABLE appointments (
            id      serial PRIMARY KEY,
            date    TIMESTAMP,
            text    TEXT,
            options TEXT,
            status  TEXT,
            span    TIMESTAMP
        )""")
    except:
        pass

    try:
        execute("""CREATE VIEW documentV AS
        SELECT documents.*, attributes.name AS category
        FROM documents JOIN attributes ON documents.category_id = attributes.id
        JOIN rols ON documents.rol_id = roles.id""")
    except:
        pass

    try:
        execute("""CREATE VIEW addressV AS
        SELECT addresses.*, attributes.name AS place
        FROM addresses JOIN attributes ON addresses.place_id = attributes.id""")
    except:
        pass





if __name__ == "__main__":
    a = Person()
    print a.status
    a. status = 'valid'
    print a.status


